#include <iostream>
#include<QtSql/QSqlDatabase>
#include<QSqlQueryModel>
#include<QSqlQuery>
#include<QSqlRecord>
#include<QDebug>
#include<QSqlTableModel>
#include<QTableView>

#include"sqlitehelper.h"

using namespace std;

SQLiteHelper::SQLiteHelper(QString DB_NAME)
{
    connectDB(DB_NAME);
}

SQLiteHelper::SQLiteHelper(QString DB_NAME,QString TB_NAME)
{
    connectDB(DB_NAME);
    createTable(TB_NAME);
}
bool SQLiteHelper::connectDB(QString DB_NAME)
{
    db=QSqlDatabase::addDatabase("QSQLITE");//使用QSqlite驱动
    db.setDatabaseName(DB_NAME);//DB名称，如果源文件没有这个DB,则会自动创建。
    if(!db.open())//如果打开失败。
    {
        return false;
    }
    else{
        return true;
    }
}

bool SQLiteHelper::closeDB(){
    //关闭
    db.close();
    return true;
}

bool SQLiteHelper::createTable(QString TB_NAME)
{
    QSqlQuery query;
    QString sql_create="CREATE TABLE IF NOT EXISTS "+TB_NAME+" (id INTEGER PRIMARY KEY AUTOINCREMENT,"
                       "name VARCHAR(20) NOT NULL,"
                       "feat TEXT NOT NULL,"
                       "imagepath VARCHAR(20) NOT NULL)";
    bool flag= query.exec(sql_create);//标记是否成功创建表
    if(flag)
    {
        qDebug()<<"ceate table success";
        return true;
    }
    else
      {
        qDebug()<<"ceate table failed";
        return false;
    }
}

bool SQLiteHelper::createTable2(QString TB_NAME)
{
    QSqlQuery query;
    QString sql_create="CREATE TABLE IF NOT EXISTS "+TB_NAME+" (id INTEGER PRIMARY KEY AUTOINCREMENT,"
                       "name VARCHAR(20) NOT NULL,"
                       "flag INTEGER NOT NULL)";
    bool flag= query.exec(sql_create);//标记是否成功创建表
    if(flag)
    {
        qDebug()<<"ceate table success";
        return true;
    }
    else
      {
        qDebug()<<"ceate table failed";
        return false;
    }
}

void SQLiteHelper::updateflag(QString TB_NAME,QString name)
{
    QSqlQuery query;
    bool b = query.exec("UPDATE "+TB_NAME+" set flag=1 where name='"+name+"'");
    cout<<b;
}

void SQLiteHelper::insert2(QString TB_NAME,QString name,int flag)
{
    QSqlTableModel model;

    model.setTable(TB_NAME);
    model.select();//=QSqlQuery::lastInsertId().toInt()
    int row=0;
    model.insertRows(row,1);//QModelParentIndex
//    model.setData(model.index(row,0),id);
    model.setData(model.index(row,1),name);
    model.setData(model.index(row,2),flag);
    model.submitAll();//输入此语句确保更新到数据库中
}

void SQLiteHelper::select2(QString TB_NAME)
{
    QSqlQuery query;
    query.exec("SELECT name,flag FROM "+TB_NAME);
    while (query.next())
    {
        qDebug()<<"log name:"<<query.value(0).toString()<<"flag"<<query.value(1).toString();
    }
}

void SQLiteHelper::selectflagno(QString TB_NAME)
{
    QSqlQuery query;
    query.exec("SELECT * FROM "+TB_NAME+" where flag=0");
    while (query.next())
    {
        qDebug()<<"log("<<query.value(0).toInt()<<")  name:"<<query.value(1).toString()<<"  是否完成:"<<query.value(2).toInt();
    }
}

void SQLiteHelper::insert(QString TB_NAME,QString name,QString feat,QString image_path)
{
    QSqlTableModel model;

    model.setTable(TB_NAME);
    model.select();//=QSqlQuery::lastInsertId().toInt()
    int row=0;
    model.insertRows(row,1);//QModelParentIndex
//    model.setData(model.index(row,0),id);
    model.setData(model.index(row,1),name);
    model.setData(model.index(row,2),feat);
    model.setData(model.index(row,3),image_path);

    model.submitAll();//输入此语句确保更新到数据库中
}
void SQLiteHelper::select(QString TB_NAME)
{
    QSqlQuery query;
    query.exec("SELECT id, name, imagepath FROM "+TB_NAME);
    while (query.next())
    {
        qDebug()<<"people("<<query.value(0).toInt()<<")  name:"<<query.value(1).toString()<<"  imagepath:"<<query.value(2).toString();
    }
}

vector<QString> SQLiteHelper::selectFeats(QString TB_NAME)
{
    QSqlQuery query;
    query.exec("SELECT feat FROM "+TB_NAME);
    vector<QString> feats;
    while (query.next())
    {
        feats.push_back(query.value(0).toString());
    }
    return feats;
}

vector<QString> SQLiteHelper::selectNames(QString TB_NAME)
{
    QSqlQuery query;
    query.exec("SELECT id,name FROM "+TB_NAME);
    vector<QString> names;
    while (query.next())
    {
        QString mixname = query.value(0).toString()+QString(":")+query.value(1).toString();
        names.push_back(mixname);
    }
    return names;
}

vector<QString> SQLiteHelper::selectIds(QString TB_NAME)
{
    QSqlQuery query;
    query.exec("SELECT id,name FROM "+TB_NAME);
    vector<QString> ids;
    while (query.next())
    {
        QString temid = query.value(0).toString();
        ids.push_back(temid);
    }
    return ids;
}

QString SQLiteHelper::selectfinalid(QString TB_NAME)
{
    QSqlQuery query;
    QString id;
    query.exec("select id from "+TB_NAME+" where id=(select max(id) from "+TB_NAME+")");
    while (query.next())
    {
        id = query.value(0).toString();
    }
    return id;
}

QString SQLiteHelper::selectpathbyid(QString id)
{
    QSqlQuery query;
    QString path;
    query.exec("select imagepath from face where id="+id);
    while (query.next())
    {
        path = query.value(0).toString();
    }
    return path;
}

int SQLiteHelper::selectAllcount(QString TB_NAME)
{
    QSqlQuery query;
    query.exec("select count(id) from "+TB_NAME);
    query.next();
    qDebug()<<query.value(0).toString();
    return query.value(0).toInt();
}

QSqlDatabase SQLiteHelper::getDbconnect()
{
    return this->db;
}

//void SQLiteHelper::selectUseModel1(QString TB_NAME)
//{
////    QSqlQuery query;
//    QSqlQueryModel model;
//    model.setQuery("SELECT * FROM "+TB_NAME);//提取所有的数据
////    for(int i=0;i<model.rowCount();i++)//rowCount=3;model的record从0到2
////    {
////        int age=model.record(i).value("age").toInt();
////        QString name=model.record(i).value("name").toString();
////        qDebug()<<name<<" "<<age;
////    }
//    int ageIndex=model.record().indexOf("age");
//    int nameIndex=model.record().indexOf("name");
//    for(int i=0;i<model.rowCount();i++)
//    {
//        QSqlRecord record=model.record(i);
//        int age=record.value(ageIndex).toInt();
//        QString name=record.value(nameIndex).toString();
//        qDebug()<<name<<" "<<ageIndex<<":"<<age;
//    }
//}

